﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetMyPostil_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetMyPostil_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetMyPostil_V2]
    @StaffName nvarchar(300)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @staffid int;

SELECT @staffid = staff_id FROM dbo.UDS_Staff WHERE staff_name = @StaffName;
IF @@rowcount <> 1
BEGIN
	raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

--待批文档
SELECT
		doc.Doc_ID,
		doc.Doc_Builder_ID,
		doc.Doc_Added_Date,
		doc.Doc_Status,
		doc.Flow_ID,
		doc.Step_ID,
		data.A as [Title],
		staff.Staff_ID,
		staff.Staff_Name,
		staff.Realname,
		flow.flow_name AS Flow_Name,
		step.step_name AS Step_Name
    FROM     
        dbo.UDS_Flow_Style_Data AS data,
        dbo.UDS_Flow_Document AS doc,
        dbo.UDS_Staff AS staff,
        dbo.UDS_Flow AS flow,
        dbo.UDS_Flow_Status AS flowStatus,
        dbo.UDS_Flow_Step AS step
    WHERE
		doc.doc_id = data.doc_ID
        and doc.flow_id = step.flow_id
        and doc.step_id = step.step_id
        and doc.flow_id = flow.flow_id
        and doc.doc_builder_id = staff.staff_id
        and doc.doc_id = flowStatus.doc_id
        and flowStatus.staff_id = @staffid
        and (
				(flowStatus.status = 0 and (doc.doc_status =2 or doc.doc_status=0))
				or flowStatus.status = 1
			)
        and doc.IsRunning = 1
    ORDER BY doc.Doc_Added_Date DESC;

END
GO